package cosplay.DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cosplay.beans.Competitions;
@Repository (value="competitiondao")
public class CompetitionDAOImpl implements CompetitionDAO {
	private JdbcTemplate jt;
	@Autowired
	public CompetitionDAOImpl(DataSource data)
	{
		this.jt=new JdbcTemplate(data);
	}

	@Override
	public int add(final Competitions competition) {
		// TODO Auto-generated method stub
		int id=0;
		if(competition!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL COMPETITION_ADD(?,?,?,?,?,?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setString(1, competition.getName());
							stm.setString(2, competition.getDescription());
							stm.setInt(3, competition.getLimit());
							stm.setTimestamp(4, competition.getStartDate());
							stm.setTimestamp(5, competition.getEndDate());
							stm.setBoolean(6, competition.isActive());
							stm.setInt(7, competition.getRank());
							stm.setInt(8, competition.getVotelimit());
							stm.registerOutParameter(9, java.sql.Types.INTEGER);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(9);
							obj[1]=rs;
							return obj;
						}});
			id=((Integer) obj[0]).intValue();
		}
		return id;
	}

	@Override
	public boolean delete(final int id) {
		// TODO Auto-generated method stub
		boolean sucess=false;
		if(id>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL COMPITITION_DELETE(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, id);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(2);
							obj[1]=rs;
							return obj;
						}});
			sucess=((Boolean) obj[0]).booleanValue();
		}
		return sucess;
	}

	@Override
	public boolean edit(final Competitions competition, final int id) {
		// TODO Auto-generated method stub
		if (id<0)
			return false;
		boolean sucess=false;
		if(competition!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL COMPETITION_EDIT(?,?,?,?,?,?,?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, id);
							stm.setString(2, competition.getName());
							stm.setString(3, competition.getDescription());
							stm.setInt(4, competition.getLimit());
							stm.setTimestamp(5, competition.getStartDate());
							stm.setTimestamp(6, competition.getEndDate());
							stm.setBoolean(7, competition.isActive());
							stm.setInt(8, competition.getRank());
							stm.setInt(9, competition.getVotelimit());
							stm.registerOutParameter(10, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(10);
							obj[1]=rs;
							return obj;
						}});
			sucess=((Boolean) obj[0]).booleanValue();
		}
		return sucess;
	}

	@Override
	public Competitions get(final int id) {
		// TODO Auto-generated method stub
		if(id>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL COMPETITION_GET(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, id);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(2);
							obj[1]=null;
							if (rs.next())
							{
								Competitions competition =new Competitions();
								competition.setActive(rs.getBoolean("active"));
								competition.setDescription(rs.getString("description"));
								competition.setEndDate(rs.getTimestamp("enddate"));
								competition.setId(rs.getInt("id"));
								competition.setLastJoined(rs.getTimestamp("lastjoined"));
								competition.setLastViewed(rs.getTimestamp("lastviewd"));
								competition.setLastVoted(rs.getTimestamp("lastvoted"));
								competition.setLimit(rs.getInt("limit"));
								competition.setName(rs.getString("name"));
								competition.setRank(rs.getInt("rank"));
								competition.setStartDate(rs.getTimestamp("startdate"));
								competition.setVotelimit(rs.getInt("votelimit"));
								obj[1]=competition;
							}
							return obj;
						}});
			if(((Boolean) obj[0]).booleanValue())
				return (Competitions) obj[1];
		}
		return null;
	}

	@Override
	public boolean activate(final int competitionid) {
		// TODO Auto-generated method stub
		if(competitionid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL COMPETITION_SETACTIVE(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, competitionid);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(2);
							obj[1]=rs;
							return obj;
						}});
				return ((Boolean) obj[0]).booleanValue();
		}
		return false;
	}

	@Override
	public boolean finish(final int competitionid) {
		// TODO Auto-generated method stub
		if(competitionid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL COMPETITION_FINISH(?,?)";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, competitionid);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(2);
							obj[1]=rs;
							return obj;
						}});
			return ((Boolean) obj[0]).booleanValue();
		}
		return false;
	}

	@Override
	public Competitions getcurrent() {
		// TODO Auto-generated method stub
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL COMPETITION_GETACTIVE();";
							CallableStatement stm=conn.prepareCall(sql);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=null;
							if (rs.next())
							{
								Competitions competition =new Competitions();
								competition.setActive(rs.getBoolean("active"));
								competition.setDescription(rs.getString("description"));
								competition.setEndDate(rs.getTimestamp("enddate"));
								competition.setId(rs.getInt("id"));
								competition.setLastJoined(rs.getTimestamp("lastjoined"));
								competition.setLastViewed(rs.getTimestamp("lastviewd"));
								competition.setLastVoted(rs.getTimestamp("lastvoted"));
								competition.setLimit(rs.getInt("limit"));
								competition.setName(rs.getString("name"));
								competition.setRank(rs.getInt("rank"));
								competition.setStartDate(rs.getTimestamp("startdate"));
								competition.setVotelimit(rs.getInt("votelimit"));
								obj[1]=competition;
							}
							return obj;
						}});
			return (Competitions) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Competitions> getall() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL COMPETITION_GETALL(?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.registerOutParameter(1, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(1);
							obj[1]=null;
							if(stm.getInt(1)>0)
							{
								List<Competitions> competitions = new ArrayList<Competitions>();
								while (rs.next())
								{
									Competitions competition =new Competitions();
									competition.setActive(rs.getBoolean("active"));
									competition.setDescription(rs.getString("description"));
									competition.setEndDate(rs.getTimestamp("enddate"));
									competition.setId(rs.getInt("id"));
									competition.setLastJoined(rs.getTimestamp("lastjoined"));
									competition.setLastViewed(rs.getTimestamp("lastviewd"));
									competition.setLastVoted(rs.getTimestamp("lastvoted"));
									competition.setLimit(rs.getInt("limit"));
									competition.setName(rs.getString("name"));
									competition.setRank(rs.getInt("rank"));
									competition.setStartDate(rs.getTimestamp("startdate"));
									competition.setVotelimit(rs.getInt("votelimit"));
									competitions.add(competition);
								}
								obj[1]=competitions;
							}
							return obj;
						}});
		if(((Integer) obj[0])>0)
			return (List<Competitions>) obj[1];
		return null;
	}

}
